﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using RM.Common;
using RM.DBUtility;
namespace RM.DAL
{
    public partial class RMDept
    {
        public RMDept()
        { }

        /// <summary>
        /// 判断数据是否存在。
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from X_Dept");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                                            new SqlParameter("@id",SqlDbType.Int)};
            parameters[0].Value = id;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 获得部门名称
        /// </summary>
        public string GetDeptName(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,name from X_Dept");
            strSql.Append(" where id=" + id);
            string deptname = Convert.ToString(DbHelperSQL.GetSingle(strSql.ToString()));
            if (string.IsNullOrEmpty(deptname))
            {
                return "";
            }
            return deptname;
        }

        /// <summary>
        /// 添加一条数据
        /// </summary>
        public int Add(Model.RMDept model)
        {
            using (SqlConnection Conn = new SqlConnection(PubConstant.ConnectionString))
            {
                Conn.Open();
                using (SqlTransaction trans = Conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("insert into X_Dept(");
                        strSql.Append("name,remark,parentdeptid,sort_id)");
                        strSql.Append(" values(");
                        strSql.Append("@name,@remark,@parentdeptid,@sortid)");
                        strSql.Append(";select @@IDENTITY");
                        SqlParameter[] parameters = {
                                            new SqlParameter("@name",SqlDbType.VarChar,50),
                                            new SqlParameter("@remark",SqlDbType.VarChar,200),
                                            new SqlParameter("@parentdeptid",SqlDbType.Int),
                                            new SqlParameter("@sortid",SqlDbType.Int)};
                        parameters[0].Value = model.Name;
                        parameters[1].Value = model.Remark;
                        parameters[2].Value = model.ParentDeptId;
                        parameters[3].Value = model.Sort_Id;
                        object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
                        model.Id = Convert.ToInt32(obj);
                    }
                    catch
                    {
                        trans.Rollback();
                        return 0;
                    }
                }
            }
            return model.Id;

        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from X_Dept");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters ={
                                          new SqlParameter("@id",SqlDbType.Int)};
            parameters[0].Value = id;
            int rows = GetParentDeptID(id);
            //如果rows大于0说明不是父ID，单独删除本条数据，如果父ID小于0或者等于0，则为父ID，那么父ID下的所有子ID全部删除，删除 条件符合ParentDeptID=ID
            if (rows == 0)
            {
                strSql.Append(";delete from X_Dept where parentdeptid=" + id);
            }
            int delRows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (delRows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
            

        }
        /// <summary>
        /// 获取父ID
        /// </summary>
        private int GetParentDeptID(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select parentdeptid from X_Dept");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters ={
                                          new SqlParameter("@id",SqlDbType.Int)};
            parameters[0].Value = id;
            int rows = Convert.ToInt32(DbHelperSQL.GetSingle(strSql.ToString(), parameters));
            if (rows > 0)
            {
                return rows;
            }
            else
            {
                return 0;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.RMDept model)
        {
            using (SqlConnection Conn = new SqlConnection(PubConstant.ConnectionString))
            {
                Conn.Open();
                using (SqlTransaction trans = Conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("update X_Dept set ");
                        strSql.Append("name=@name,");
                        strSql.Append("remark=@remark,");
                        strSql.Append("parentdeptid=@parentdeptid,");
                        strSql.Append("sort_id=@sort_id");
                        strSql.Append(" where id=@id");
                        SqlParameter[] parameters ={
                                                      new SqlParameter("@name",SqlDbType.VarChar,50),
                                                      new SqlParameter("@remark",SqlDbType.VarChar,200),
                                                      new SqlParameter("@parentdeptid",SqlDbType.Int),
                                                      new SqlParameter("@sort_id",SqlDbType.Int),
                                                      new SqlParameter("@id",SqlDbType.Int)};
                        parameters[0].Value = model.Name;
                        parameters[1].Value = model.Remark;
                        parameters[2].Value = model.ParentDeptId;
                        parameters[3].Value = model.Sort_Id;
                        parameters[4].Value = model.Id;
                        DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return false;
                    }
                }
            }
            return true;
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.RMDept GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 * from X_Dept");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters ={
                                          new SqlParameter("@id",SqlDbType.Int)};
            parameters[0].Value = id;
            Model.RMDept model = new Model.RMDept();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                model.Name = ds.Tables[0].Rows[0]["name"].ToString();
                model.Remark = ds.Tables[0].Rows[0]["remark"].ToString();
                if (ds.Tables[0].Rows[0]["parentdeptid"].ToString() != "")
                {
                    model.ParentDeptId = int.Parse(ds.Tables[0].Rows[0]["parentdeptid"].ToString());
                }
                if (ds.Tables[0].Rows[0]["sort_id"].ToString() != "")
                {
                    model.Sort_Id = int.Parse(ds.Tables[0].Rows[0]["sort_id"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 获取查询分页数据
        /// </summary>
        /// <param name="pageSize">每页数量</param>
        /// <param name="pageIndex">从第几页开始</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="fieldOrder">按照哪个字段排序</param>
        /// <param name="recordCount">数据数量，输出变量</param>
        /// <returns></returns>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string fieldOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from X_Dept");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), fieldOrder));
        }
    }
}
